using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using eShop.Model;
using System.Collections.Generic;
using eShop.Utility;


namespace eShop.DataAccess
{
    public class ManufacturerDAO : DatabaseUtility
    {
        public static List<Manufacturer> GetProductBrandsByPertnerCategory(int categoryId)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductBrand_GetAllTblProductBrandByPartnerCategoryId");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductBrandCollectionFromReader);
            List<Manufacturer> objCollection = ((List<Manufacturer>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static int GetProductBrandCountByName(string brandName)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@BrandName", SqlDbType.NVarChar, 500, ParameterDirection.Input, brandName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTBRAND_GET_PRODUCT_BRAND_COUNT_BY_NAME);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static Manufacturer GetTblProductBrandByProductBrandUrl(string brandUrl)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandName", SqlDbType.NVarChar, 500, ParameterDirection.Input, brandUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductBrand_GetProductBrandByUrl");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductBrandCollectionFromReader);
            List<Manufacturer> objCollection = ((List<Manufacturer>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static Int32 CreateNewTblProductBrand
        (
            String brandName,
            String brandDescription,
            String brandImageUrl,
            String brandUrl,
            Boolean isActive,
            String brandBannerUrl
        )
        {
            //creating an empty TblProductBrand object
            Manufacturer item = new Manufacturer();
            item.BrandName = brandName;
            item.BrandDescription = brandDescription;
            item.BrandImageUrl = brandImageUrl;
            item.BrandUrl = brandUrl;
            item.IsActive = isActive;
            item.BrandBannerImageUrl = brandBannerUrl;

            return ManufacturerDAO.CreateNewTblProductBrand(item);
        }

        public static bool UpdateTblProductBrand
        (
            Int32 productBrandId,
            String brandName,
            String brandDescription,
            String brandImageUrl,
            String brandUrl,
            Boolean isActive,
            String brandBannerUrl
        )
        {
            Manufacturer item = ManufacturerDAO.GetTblProductBrandByProductBrandId(productBrandId);

            item.BrandName = brandName;
            item.BrandDescription = brandDescription;
            item.BrandImageUrl = brandImageUrl;
            item.BrandUrl = brandUrl;
            item.IsActive = isActive;
            item.BrandBannerImageUrl = brandBannerUrl;

            return ManufacturerDAO.UpdateTblProductBrand(item);
        }

        public static bool DeleteTblProductBrand
        (
            Int32 productBrandId
        )
        {
            //Validate Input
            if (productBrandId <= GetProductBrandIdMinValue)
                throw (new ArgumentOutOfRangeException("productBrandId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, productBrandId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_DELETETBLPRODUCTBRAND);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblProductBrandList(System.Collections.Generic.List<Int32> productBrandIdListToDelete)
        {
            //Validate Input
            foreach (Int32 productBrandId in productBrandIdListToDelete)
                if (productBrandId <= GetProductBrandIdMinValue)
                    throw (new ArgumentOutOfRangeException("productBrandId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(productBrandIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductBrandIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_DELETETBLPRODUCTBRANDLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == productBrandIdListToDelete.Count ? true : false);
        }

        public static Manufacturer GetTblProductBrandByProductBrandId(Int32 productBrandId)
        {
            if (productBrandId <= GetProductBrandIdMinValue)
                throw (new ArgumentOutOfRangeException("productBrandId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, productBrandId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_GETTBLPRODUCTBRANDBYPRODUCTBRANDID);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductBrandCollectionFromReader);
            List<Manufacturer> items = ((List<Manufacturer>)ExecuteReaderCmd(sqlCmd, reader));

            if (items.Count > 0)
                return items[0];
            else
                return null;
        }

        public static List<Manufacturer> GetAllTblProductBrand()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_GETALLTBLPRODUCTBRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductBrandCollectionFromReader);
            List<Manufacturer> items = ((List<Manufacturer>)ExecuteReaderCmd(sqlCmd, reader));

            return items;
        }

        public static List<Manufacturer> GetAllTblProductBrandPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ProductBrandId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_GETALLTBLPRODUCTBRANDPAGED);

            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductBrandCollectionFromReader);
            List<Manufacturer> items = ((List<Manufacturer>)ExecuteReaderCmd(sqlCmd, reader));
            return items;
        }

        public static int GetAllTblProductBrandPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_GETALLTBLPRODUCTBRANDPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(Manufacturer item)
        {
            if (item.ProductBrandId <= GetProductBrandIdMinValue)
            {
                item.ProductBrandId = CreateNewTblProductBrand(item);
                if (item.ProductBrandId > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return (UpdateTblProductBrand(item));
            }

        }

        public static Int32 CreateNewTblProductBrand(Manufacturer item)
        {
            // validate Parameters
            if (item == null)
                throw (new ArgumentNullException("newTblProductBrand"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@BrandName", SqlDbType.NVarChar, 500, ParameterDirection.Input, item.BrandName);
            AddParameterToSqlCmd(sqlCmd, "@BrandDescription", SqlDbType.NVarChar, 4000, ParameterDirection.Input, item.BrandDescription);
            AddParameterToSqlCmd(sqlCmd, "@BrandImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, string.IsNullOrEmpty(item.BrandImageUrl) ? "" : item.BrandImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@BrandUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, item.BrandUrl);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, item.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@DateCreated", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParameterToSqlCmd(sqlCmd, "@BrandCode", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@BrandBannerUrl", SqlDbType.NVarChar, -1, ParameterDirection.Input, string.IsNullOrEmpty(item.BrandBannerImageUrl) ? "" : item.BrandBannerImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_CREATENEWTBLPRODUCTBRAND);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblProductBrand(Manufacturer item)
        {
            // validate Parameters
            if (item == null)
                throw (new ArgumentNullException("newTblProductBrand"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductBrandId", SqlDbType.Int, 0, ParameterDirection.Input, item.ProductBrandId);
            AddParameterToSqlCmd(sqlCmd, "@BrandName", SqlDbType.NVarChar, 500, ParameterDirection.Input, item.BrandName);
            AddParameterToSqlCmd(sqlCmd, "@BrandDescription", SqlDbType.NVarChar, 4000, ParameterDirection.Input, item.BrandDescription);
            AddParameterToSqlCmd(sqlCmd, "@BrandImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, string.IsNullOrEmpty(item.BrandImageUrl) ? "" : item.BrandImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@BrandUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, item.BrandUrl);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, item.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@DateCreated", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParameterToSqlCmd(sqlCmd, "@BrandCode", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@BrandBannerUrl", SqlDbType.NVarChar, -1, ParameterDirection.Input, string.IsNullOrEmpty(item.BrandBannerImageUrl) ? "" : item.BrandBannerImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTBRAND_UPDATETBLPRODUCTBRAND);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblProductBrandCollectionFromReader(IDataReader returnData)
        {
            List<Manufacturer> items = new List<Manufacturer>();
            while (returnData.Read())
            {
                Manufacturer item = new Manufacturer()
                {
                    ProductBrandId = returnData["ProductBrandId"] == System.DBNull.Value ? GetProductBrandIdMinValue : (Int32)returnData["ProductBrandId"],
                    BrandName = returnData["BrandName"] == System.DBNull.Value ? string.Empty : (String)returnData["BrandName"],
                    BrandDescription = returnData["BrandDescription"] == System.DBNull.Value ? string.Empty : (String)returnData["BrandDescription"],
                    BrandImageUrl = returnData["BrandImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["BrandImageUrl"],
                    BrandUrl = returnData["BrandUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["BrandUrl"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    BrandBannerImageUrl = returnData["BrandBannerUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["BrandBannerUrl"]
                };
                items.Add(item);
            }

            return (items);
        }

        public static Int32 GetProductBrandIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SPR_TBLPRODUCTBRAND_CREATENEWTBLPRODUCTBRAND = "spr_TblProductBrand_CreateNewTblProductBrand";
        internal const string SPR_TBLPRODUCTBRAND_UPDATETBLPRODUCTBRAND = "spr_TblProductBrand_UpdateTblProductBrand";
        internal const string SPR_TBLPRODUCTBRAND_DELETETBLPRODUCTBRAND = "spr_TblProductBrand_DeleteTblProductBrand";
        internal const string SPR_TBLPRODUCTBRAND_DELETETBLPRODUCTBRANDLIST = "spr_TblProductBrand_DeleteTblProductBrandList";
        internal const string SPR_TBLPRODUCTBRAND_GETTBLPRODUCTBRANDBYPRODUCTBRANDID = "spr_TblProductBrand_GetTblProductBrandByProductBrandId";
        internal const string SPR_TBLPRODUCTBRAND_GETALLTBLPRODUCTBRAND = "spr_TblProductBrand_GetAllTblProductBrand";
        internal const string SPR_TBLPRODUCTBRAND_GETALLTBLPRODUCTBRANDPAGED = "spr_TblProductBrand_GetAllTblProductBrandPaged";
        internal const string SPR_TBLPRODUCTBRAND_GETALLTBLPRODUCTBRANDPAGEDCOUNT = "spr_TblProductBrand_GetAllTblProductBrandPagedCount";

        private const string SP_TBL_PRODUCTBRAND_GET_TBL_PRODUCT_BRAND_BY_NAME = "sp_TblProductBrand_Get_ProductBrand_By_Name";
        private const string SP_TBL_PRODUCTBRAND_GET_TBL_PRODUCT_BRAND_BY_PREFIX = "sp_TblProductBrand_Get_ProductBrand_By_Prefix";
        private const string SP_TBL_PRODUCTBRAND_GET_TBL_PRODUCT_BRAND_BY_CONDITION = "sp_TblProductBrand_Get_ProductBrand_By_Condition";
        private const string SP_TBL_PRODUCTBRAND_GET_TBL_PRODUCT_BRAND_FOR_FEATURED = "sp_TblProductBrand_Get_ProductBrand_For_Featured";
        private const string SP_TBL_PRODUCTBRAND_GET_TBL_PRODUCT_BRAND_ACTIVE = "sp_TblProductBrand_Get_ProductBrand_Active";
        private const string SP_TBL_PRODUCTBRAND_GET_PRODUCT_BRAND_COUNT_BY_NAME = "sp_TblProductBrand_Get_ProductBrand_Count_By_Name";
        private const string SP_TBLPRODUCTBRAND_TEMPORARY_DELETE_BRANDS = "sp_TblProductBrand_Temporary_Delete_Brands";
        private const string SP_TBL_PRODUCTBRAND_GET_TBL_PRODUCT_BRAND_NONDELETED = "sp_TblProductBrand_Get_ProductBrand_NonDeleted";
    }
}
